package com.osfw.dbadminer.service.jdbc.impl;

import com.osfw.framework.utils.StringUtils;
import com.osfw.dbadminer.domain.database.HintSqlParam;
import com.osfw.dbadminer.domain.database.SqlExecuteParam;
import com.osfw.dbadminer.domain.database.TableColDef;
import com.osfw.dbadminer.domain.database.TableDef;
import com.osfw.dbadminer.entity.connect.DbaConnect;
import com.osfw.dbadminer.service.jdbc.JdbcBaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class MysqlJdbcService extends JdbcBaseService {

    @Override
    public void init(DbaConnect connect) throws SQLException {
        driver = new com.mysql.cj.jdbc.Driver();
        connect_prefix = "jdbc:mysql://";
        default_database = StringUtils.isNotEmpty(connect.getDatabase()) ? connect.getDatabase() : "mysql";
        connect_params = "?useSSL=false&serverTimezone=UTC";
        myconnect = connect;
    }

    @Override
    public String buildConnectUrl(String database) {
        if (StringUtils.isNotEmpty(database)) {
            database = "/" + database;
        }
        return connect_prefix + myconnect.getHost() + ":" + myconnect.getPort() + database + connect_params;
    }

    // 获取所有数据库
    @Override
    public List<String> getDatabaseList() throws SQLException {
        List<String> databaseList = new ArrayList<>();
        try (Connection connection = this.getConnection(default_database);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SHOW DATABASES");) {
            while (resultSet.next()) {
                databaseList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            throw e;
        }
        return databaseList;
    }

    // 获取所有数据表
    @Override
    public List<TableDef> getTableList(String database) throws SQLException {
        List<TableDef> tableList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", null);
            while (tables.next()) {
                tableList.add(new TableDef(database,
                        tables.getString("TABLE_SCHEM"), tables.getString("TABLE_NAME"),
                        tables.getString("TABLE_TYPE"), tables.getString("REMARKS")));
            }
            tables.close();
        } catch (SQLException e) {
            throw e;
        }

        return tableList;
    }

    // 获取所有数据视图
    @Override
    public List<String> getViewList(String database) throws SQLException {
        List<String> tableList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", "VIEW".split(","));
            while (tables.next()) {
                tableList.add(tables.getString("TABLE_NAME"));
            }
            tables.close();
        } catch (SQLException e) {
            throw e;
        }
        return tableList;
    }

    // 获取所有数据视图
    @Override
    public List<String> getFunctionList(String database) throws SQLException {
        List<String> functionList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet functions = metaData.getFunctions(database, null, "%");
            while (functions.next()) {
                functionList.add(functions.getString("FUNCTION_NAME"));
            }
            functions.close();
        } catch (SQLException e) {
            throw e;
        }
        return functionList;
    }

    @Override
    public List<String> getIndexList(String database) throws SQLException {
        List<String> tableList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", "INDEX".split(","));
            while (tables.next()) {
                tableList.add(tables.getString("TABLE_NAME"));
            }
            tables.close();
        } catch (SQLException e) {
            throw e;
        }
        return tableList;
    }

    @Override
    public List<String> getSequenceList(String database) throws SQLException {
        List<String> tableList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", "SEQUENCE".split(","));
            while (tables.next()) {
                tableList.add(tables.getString("TABLE_NAME"));
            }
            tables.close();
        } catch (SQLException e) {
            throw e;
        }
        return tableList;
    }

    @Override
    public List<String> getTypeList(String database) throws SQLException {
        List<String> tableList = new ArrayList<>();
        try (Connection connection = getConnection(database);) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", "TYPE".split(","));
            while (tables.next()) {
                tableList.add(tables.getString("TABLE_NAME"));
            }
            tables.close();
        } catch (SQLException e) {
            throw e;
        }
        return tableList;
    }

    @Deprecated
    public Map<String, Object> getHintTableColumnDeprecated(HintSqlParam hintSqlParam) throws SQLException {
        Map<String, Object> hintMap = new LinkedHashMap<>();
        List<TableColDef> tableColDefList = new ArrayList<>();
        try(Connection connection = getConnection(hintSqlParam.getDatabase());){
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(hintSqlParam.getDatabase(), null, "%", "TABLE".split(","));
            while (tables.next()) {
                Map<String, Object> content = new LinkedHashMap();
                content.put("array", new ArrayList());
                content.put("type", "table");
                content.put("displayText", tables.getString("TABLE_NAME"));
                content.put("source", "   " + hintSqlParam.getDatabase());
                hintMap.put(tables.getString("TABLE_NAME"), content);
            }
            hintMap = hintMap.entrySet().stream()
                    .filter(entry -> hintSqlParam.getTableNames() == null || hintSqlParam.getTableNames().isEmpty() || !hintSqlParam.getTableNames().contains(entry.getKey()))
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
            if(hintSqlParam.getTableNames() != null || !hintSqlParam.getTableNames().isEmpty()) {
                ResultSet columns = metaData.getColumns(hintSqlParam.getDatabase(), null, "%", "%");
                while (columns.next()) {
                    TableColDef colDef = new TableColDef();
                    colDef.setDatabase(hintSqlParam.getDatabase());
                    colDef.setTableName(columns.getString("TABLE_NAME"));
                    colDef.setColumnName(columns.getString("COLUMN_NAME"));
                    colDef.setColumnTypeName(columns.getString("TYPE_NAME"));
                    tableColDefList.add(colDef);
                }
                tableColDefList = tableColDefList.stream().filter(x -> hintSqlParam.getTableNames().contains(x.getTableName())).collect(Collectors.toList());
                Map<String, List<TableColDef>> map = tableColDefList.stream().collect(Collectors.groupingBy(TableColDef::getTableName));
                Map<String, Object> finalHintMap = hintMap;
                map.entrySet().stream().forEach(entry -> {
                    Map<String, Object> content = new LinkedHashMap();
                    List<Map<String, Object>> array = entry.getValue().stream().map(x ->
                            new HashMap<String, Object>(){{
                                put("text", x.getColumnName());
                                put("type", "column");
                                put("displayText", String.join("::", x.getColumnName(), "["+ x.getColumnTypeName()+"]"));
                                put("source", "   " + String.join(".", hintSqlParam.getDatabase(), x.getTableName()));
                            }}
                    ).collect(Collectors.toList());
                    content.put("array", array);
                    content.put("type", "table");
                    content.put("displayText", entry.getKey());
                    content.put("source", "   " + hintSqlParam.getDatabase());
                    finalHintMap.put(entry.getKey(), content);
                });
                Map<String, Object> finalHintMap1 = hintMap;
                tableColDefList.stream().sorted(Comparator.comparing(TableColDef::getTableName)).forEach(x -> {
                    Map<String, Object> content = new LinkedHashMap();
                    content.put("array", new ArrayList());
                    content.put("type", "column");
                    content.put("displayText", String.join("::", x.getColumnName(), "["+ x.getColumnTypeName()+"]"));
                    content.put("source", "   " + String.join(".", hintSqlParam.getDatabase(), x.getTableName()));
                    finalHintMap1.put(x.getColumnName(), content);
                });
                columns.close();
            }
            tables.close();
            return hintMap;
        }catch (SQLException e){
            throw e;
        }
    }
    @Override
    public Map<String, Object> getHintTableColumn(HintSqlParam hintSqlParam) throws SQLException {
        Map<String, Object> hintMap = new LinkedHashMap<>();
        List<TableColDef> tableColDefList = new ArrayList<>();
        try (Connection connection = getConnection(hintSqlParam.getDatabase());) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(hintSqlParam.getDatabase(), null, "%", "%");
            while (columns.next()) {
                TableColDef colDef = new TableColDef();
                colDef.setDatabase(hintSqlParam.getDatabase());
                colDef.setTableName(columns.getString("TABLE_NAME"));
                colDef.setColumnName(columns.getString("COLUMN_NAME"));
                colDef.setColumnTypeName(columns.getString("TYPE_NAME"));
                tableColDefList.add(colDef);
            }
            Map<String, List<TableColDef>> map = tableColDefList.stream().collect(Collectors.groupingBy(TableColDef::getTableName));
            Map<String, Object> finalHintMap = hintMap;
            map.entrySet().stream().forEach(entry -> {
                Map<String, Object> content = new LinkedHashMap();
                List<Map<String, Object>> array = entry.getValue().stream().map(x ->
                        new HashMap<String, Object>() {{
                            put("text", x.getColumnName());
                            put("type", "column");
                            put("displayText", String.join("::", x.getColumnName(), "[" + x.getColumnTypeName() + "]"));
                            put("source", "   " + String.join(".", hintSqlParam.getDatabase(), x.getTableName()));
                        }}
                ).collect(Collectors.toList());
                content.put("array", array);
                content.put("type", "table");
                content.put("displayText", entry.getKey());
                content.put("source", "   " + hintSqlParam.getDatabase());
                finalHintMap.put(entry.getKey(), content);
            });
            Map<String, Object> finalHintMap1 = hintMap;
            tableColDefList.stream().sorted(Comparator.comparing(TableColDef::getTableName)).forEach(x -> {
                Map<String, Object> content = new LinkedHashMap();
                content.put("array", new ArrayList());
                content.put("type", "column");
                content.put("displayText", String.join("::", x.getColumnName(), "[" + x.getColumnTypeName() + "]"));
                content.put("source", "   " + String.join(".", hintSqlParam.getDatabase(), x.getTableName()));
                finalHintMap1.put(x.getColumnName(), content);
            });
            columns.close();
            return hintMap;
        } catch (SQLException e) {
            throw e;
        }
    }

    // 获取数据表所有字段
    @Override
    public List<String> getAllColumns(String database, String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        try (Connection connection = getConnection(database); Statement statement = connection.createStatement();) {
            String sql = "select * from " + tableName;
            ResultSet resultSet = statement.executeQuery(sql);
            int columnCount = resultSet.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                columns.add(resultSet.getMetaData().getColumnName(i));
            }
            resultSet.close();
        } catch (SQLException e) {
            throw e;
        }
        return columns;
    }


    // 获取数据表所有索引
    @Override
    public List<String> getAllIndex(String database, String tableName) throws SQLException {
        List<String> allIndex = new ArrayList<>();
        try (Connection connection = getConnection(database); Statement statement = connection.createStatement();) {
            String sql = "show index from " + tableName;
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                allIndex.add(resultSet.getString("Key_name"));
            }
            resultSet.close();
        } catch (SQLException e) {
            throw e;
        }
        return allIndex;
    }

    // 获取表格内容
    @Override
    public Object getTableContent(SqlExecuteParam sqlExecuteParam) throws SQLException {
        List<Map<String, String>> result = new ArrayList<>();
        List<String> thead = new ArrayList<>();
        Object res = execute(sqlExecuteParam);
        if (!(res instanceof ResultSet)) {
            return res;
        }
        try (ResultSet resultSet = (ResultSet) res;) {
            int columnCount = resultSet.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                thead.add(resultSet.getMetaData().getColumnName(i));
            }
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>();
                for (String col : thead) {
                    map.put(col, resultSet.getString(col));
                }
                result.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    // 创建表格
    @Override
    public boolean createTable(TableDef tableDef) throws SQLException {
        try (Connection connection = getConnection(tableDef.getDatabase()); Statement statement = connection.createStatement();) {
            String createTableSql = buildCreateTableSql(tableDef);
            return statement.execute(createTableSql);
        } catch (SQLException e) {
            throw e;
        }
    }


    private String buildCreateTableSql(TableDef tableDef) {
        StringBuilder createTableSql = new StringBuilder(" create Table " + tableDef.getTableName() + " ( ");
        List<TableColDef> tableColDefs = tableDef.getTableColDefs();
        tableColDefs.forEach(tableColDef -> {
            createTableSql.append(tableColDef.getColumnName() + " " + tableColDef.getColumnTypeName() + "(" + tableColDef.getColumnLength() + "),");
        });
        String result = createTableSql.substring(0, createTableSql.length() - 1);
        result += ");";
        return result;
    }


}
